package com.ctg.behavior.calc.impala.transfer.ltv;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.alibaba.fastjson.JSON;
import com.ctg.behavior.calc.common.CtCalcDate;
import com.ctg.behavior.calc.common.CtSQLParam;
import com.ctg.behavior.calc.impala.report.ltv.CtLTVMeasureReport;
import com.ctg.behavior.calc.impala.transfer.CtFilterTransfer;
import com.ctg.behavior.calc.utils.CtCalcPropertyUtils;
import com.ctg.behavior.calc.utils.CtCalcSQLUtils;
import com.ctg.behavior.common.calc.CtCalc;
import com.ctg.behavior.common.calc.CtCalcFilter;
import com.ctg.behavior.common.calc.CtCalcPropertyType;
import com.ctg.behavior.common.consts.CtFieldConsts;
import com.ctg.behavior.common.consts.CtPropertyConsts;
import com.ctg.behavior.utils.CtDateUtils;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Component
public class CtLTVMeasureTransfer extends CtFilterTransfer {

	@Override
	public String doTransfer(CtCalc calcObject, CtSQLParam sqlParam) {

		if (!(calcObject instanceof CtLTVMeasureReport)) {
			log.warn("calcObject is not CtLTVMeasureReport, class={}", calcObject.getClass());
			log.warn("calcObject: {}", JSON.toJSON(calcObject));
			return "";
		}
		CtLTVMeasureReport report = (CtLTVMeasureReport) calcObject;
		log.info("measure={}", JSON.toJSONString(report));

		List<CtCalcPropertyType> byPropertis = new ArrayList<>();
		{
			List<String> byFields = report.getByFields();
			for (String oneField : byFields) {
				CtCalcPropertyType ptype = CtCalcPropertyUtils.getValueType(oneField);
				byPropertis.add(ptype);
			}
		}

		StringBuilder impalaSQL = new StringBuilder();

		String baseCellSQL = this.transferLTVBaseCell(report, byPropertis);
		log.info("baseCellSQL={}", baseCellSQL);
		impalaSQL.append(baseCellSQL);

		for (int ltvIndex = 0; ltvIndex < report.getCellDates().size(); ltvIndex++) {
			CtCalcDate ltvMeasure = report.getCellDates().get(ltvIndex);
			String oneCellSQL = transferLTVMeasureCell(report, ltvIndex, ltvMeasure, byPropertis);
			log.info("oneCellSQL={}", oneCellSQL);
			impalaSQL.append(" UNION ").append(oneCellSQL);
		}

		return impalaSQL.toString();

	}

	public String transferLTVBaseCell(CtLTVMeasureReport report, List<CtCalcPropertyType> byPropertis) {

		StringBuilder impalaSQL = new StringBuilder();

		// 1. SELECT 部分：
		{
			impalaSQL.append(" SELECT \"").append(report.getWhichDay()).append("\" AS ").append(CtFieldConsts.FIELD_KEY_CALC_DATE);
			// impalaSQL.append(" , T0.event AS event ");
			impalaSQL.append(" , \"").append(report.getMeasure().getEventId()).append("\" AS event ");
			impalaSQL.append(" , \"-1\" AS ltv ");
			for (CtCalcPropertyType byProperty : byPropertis) {
				String property = byProperty.getProperty();
				impalaSQL.append(" , T0.").append(property).append(" AS ").append(property);
			}
			impalaSQL.append(" , count(*) AS total ");
		}
		{
			String userIdsSQL = this.transferStartSignUserIds(report, byPropertis);
			impalaSQL.append(" FROM ( ").append(userIdsSQL).append(" ) AS T0 ");
		}
		// 分组(group by)
		{
			impalaSQL.append(" GROUP BY T0.event ");
			for (CtCalcPropertyType byProperty : byPropertis) {
				impalaSQL.append(", T0.").append(byProperty.getProperty());
			}
		}

		return impalaSQL.toString();
	}

	public String transferLTVMeasureCell(CtLTVMeasureReport report, Integer ltvIndex, CtCalcDate cellDate, List<CtCalcPropertyType> byPropertis) {

		StringBuilder impalaSQL = new StringBuilder();

		// 1. SELECT 部分：
		{
			impalaSQL.append(" SELECT \"").append(report.getWhichDay()).append("\" AS ").append(CtFieldConsts.FIELD_KEY_CALC_DATE);
			impalaSQL.append(" , te02.event AS event ");
			impalaSQL.append(" , \"").append(ltvIndex).append("\" AS ltv ");
			for (CtCalcPropertyType byProperty : byPropertis) {
				String property = byProperty.getProperty();
				if (CtPropertyConsts.PROPERTY_EVENT.equals(byProperty.getType())) {
					impalaSQL.append(" , te02.").append(property).append(" AS ").append(property);
				} else if (CtPropertyConsts.PROPERTY_USER.equals(byProperty.getType())) {
					impalaSQL.append(" , tu02.").append(property).append(" AS ").append(property);
				}
			}
			if (report.getIsEventTimes()) {
				// 计算事件的总次数
				impalaSQL.append(" , count(*) AS total ");
			} else {
				// 计算事件属性值,如：拼单支付成功(事件)的支付额度(属性)
				String measureField = report.getMeasure().getField();
				CtCalcPropertyType mearsureEvent = CtCalcPropertyUtils.getValueType(measureField);
				impalaSQL.append(" , SUM(te02.").append(mearsureEvent.getProperty()).append(") AS total ");
			}
		}
		// 2. 表关联
		{
			impalaSQL.append(" FROM a_event_parquet_").append(report.getAppId()).append(" te02 ");
			if (!report.getFilter().getConditions().isEmpty() || !byPropertis.isEmpty()) {
				// 2.1 如果有用户属性筛选条件，则进行用户表关联( a_user_kudu_{appId})
				impalaSQL.append(" JOIN  a_user_kudu_").append(report.getAppId()).append(" tu02 ");
				impalaSQL.append(" ON te02.user_id = tu02.user_id ");
			}
			// 2.2 Second Event的用户必在First Event的用户中，且时间要晚于First Event
			String startSignUserIdsSQL = this.transferStartSignUserIds(report, byPropertis);
			impalaSQL.append(" JOIN ( ").append(startSignUserIdsSQL).append(" ) tuj ");
			impalaSQL.append(" ON te02.user_id = tuj.userId ");
			for (CtCalcPropertyType byProperty : byPropertis) {
				String property = byProperty.getProperty();
				if (CtPropertyConsts.PROPERTY_EVENT.equals(byProperty.getType())) {
					impalaSQL.append(" AND te02.").append(property).append(" = tuj.").append(property);
				} else if (CtPropertyConsts.PROPERTY_USER.equals(byProperty.getType())) {
					impalaSQL.append(" AND tu02.").append(property).append(" = tuj.").append(property);
				}
			}

			impalaSQL.append(" WHERE 1=1 ");
		}
		// 3. 营收事件及其的筛选条件(filter)
		{
			// impalaSQL.append(" AND ( ");
			// 营收事件限制
			if (report.getIsEventTimes()) {
				String secondEventId = report.getMeasure().getEventId();
				impalaSQL.append(" AND te02.event = '").append(secondEventId).append("' ");
			}
			// 营收事件的筛选条件
			CtCalcFilter filter = report.getMeasure().getFilter();
			String conditionsSQL = super.doTransfer(filter, CtSQLParam.createAliasParam("te02"));
			if (!CtCalcSQLUtils.isEmptyCondtions(conditionsSQL)) {
				impalaSQL.append(" AND ").append(conditionsSQL);
			}
			// 营收事件 - 本Cell的时间限制 - fromDate/toDate
			String toDateText = CtDateUtils.dateToString(cellDate.getToDate(), CtDateUtils.YYYY_MM_DD);
			String fromDateText = CtDateUtils.dateToString(cellDate.getFromDate(), CtDateUtils.YYYY_MM_DD);
			impalaSQL.append(" AND te02.event_date <= '").append(toDateText).append("' ");
			impalaSQL.append(" AND te02.event_date >= '").append(fromDateText).append("' ");
			// impalaSQL.append(" ) ");
		}

		// 4. 查询条件 - 且用户符合
		{
			CtCalcFilter filter = report.getFilter();
			String conditionsSQL = super.doTransfer(filter, CtSQLParam.createAliasParam("tu02"));
			if (!CtCalcSQLUtils.isEmptyCondtions(conditionsSQL)) {
				impalaSQL.append(" AND ").append(conditionsSQL);
			}
		}
		// 5. 分组(group by) - 区分
		{
			impalaSQL.append(" GROUP BY te02.event ");
			for (CtCalcPropertyType byProperty : byPropertis) {
				String property = byProperty.getProperty();
				if (CtPropertyConsts.PROPERTY_EVENT.equals(byProperty.getType())) {
					impalaSQL.append(" , ").append(" te02.").append(property);
				} else if (CtPropertyConsts.PROPERTY_USER.equals(byProperty.getType())) {
					impalaSQL.append(" , ").append(" tu02.").append(property);
				}
			}
		}

		return impalaSQL.toString();
	}

	public String transferStartSignUserIds(CtLTVMeasureReport report, List<CtCalcPropertyType> byPropertis) {

		StringBuilder impalaSQL = new StringBuilder();

		// 1. SELECT 部分：
		{
			impalaSQL.append(" SELECT te01.user_id AS userId, te01.event AS event ");
			for (CtCalcPropertyType byProperty : byPropertis) {
				if (CtPropertyConsts.PROPERTY_EVENT.equals(byProperty.getType())) {
					impalaSQL.append(" , te01.").append(byProperty.getProperty()).append(" AS ").append(byProperty.getProperty());
				} else if (CtPropertyConsts.PROPERTY_USER.equals(byProperty.getType())) {
					impalaSQL.append(" , tu01.").append(byProperty.getProperty()).append(" AS ").append(byProperty.getProperty());
				}
			}
		}
		// 2. 表关联
		{
			impalaSQL.append(" FROM a_event_parquet_").append(report.getAppId()).append(" te01 ");
			if (!report.getFilter().getConditions().isEmpty() || !byPropertis.isEmpty()) {
				// 如果有用户属性筛选条件，则进行用户表关联( a_user_kudu_{appId})
				impalaSQL.append(" LEFT JOIN  a_user_kudu_").append(report.getAppId()).append(" tu01 ");
				impalaSQL.append(" ON te01.user_id = tu01.user_id ");
			}
			impalaSQL.append(" WHERE 1=1 ");
		}
		// 3. 初始事件及其的筛选条件(filter) 或者 用户时间限制条件
		{
			impalaSQL.append(" AND ( ");
			if (!StringUtils.isEmpty(report.getStartSign().getStartEvent())) {
				// 为：初始事件
				String startEvent = report.getStartSign().getStartEvent();
				impalaSQL.append(" te01.event = '").append(startEvent).append("' ");
				CtCalcFilter filter = report.getStartSign().getFilters();
				String conditionsSQL = super.doTransfer(filter, CtSQLParam.createAliasParam("te01"));
				if (!CtCalcSQLUtils.isEmptyCondtions(conditionsSQL)) {
					impalaSQL.append(" AND ").append(conditionsSQL);
				}
			} else {
				// 为：初始时间(用户属性)

			}
			impalaSQL.append(" ) ");
		}
		// 4. 查询条件 - 且用户符合
		{
			CtCalcFilter filter = report.getFilter();
			String conditionsSQL = super.doTransfer(filter, CtSQLParam.createAliasParam("tu01"));
			if (!CtCalcSQLUtils.isEmptyCondtions(conditionsSQL)) {
				impalaSQL.append(" AND ").append(conditionsSQL);
			}
		}
		// 5. 时间限制
		{
			impalaSQL.append(" AND te01.event_date = '").append(report.getWhichDay()).append("' ");
		}
		// 6. 分组(group by) - 区分
		{
			impalaSQL.append(" GROUP BY ");
			impalaSQL.append(" te01.user_id , te01.event ");
			for (CtCalcPropertyType byProperty : byPropertis) {
				if (CtPropertyConsts.PROPERTY_EVENT.equals(byProperty.getType())) {
					impalaSQL.append(", te01.").append(byProperty.getProperty());
				} else if (CtPropertyConsts.PROPERTY_USER.equals(byProperty.getType())) {
					impalaSQL.append(", tu01.").append(byProperty.getProperty());
				}
			}
		}

		return impalaSQL.toString();
	}

}
